prompt 门诊院内扣费记账以及结算 create or replace procedure SP_SF_FYXX00_JS0000 ( PID0000 in number, --ID0000 PBRID00 in number, --病人ID PCZY000 in number , --操作员 PFPXH00 in varchar2, --发票序号 PTSBZBH in varchar default '', --特殊病种编号 PSFDYFP in varchar2 default 'Y,N', --打印发票标志(第一位:Y打印N不打印,第二位：N打印发票 Y打印结算单 M不打印发票和结算单 PJZDH00 out number, --结账单号 pYHMSG0 out varchar2, --存储过程提示的错误信息 PSYSMSG out varchar2 --系统提示的错误信息 ) -- MODIFICATION HISTORY -- Person Date Comments --zhangyc 2015.04.28 create by MZSF-20150428-001 --zhangyc 2015.05.14 自费结算不判断 金额是否一致 by MZSF-20150514-001 --zhangyc 2015.05.21 增加获取批次价格 by MZSF-20150521-001 --zhangyc 2015.07.13 修改公费病人提示补交金额错误 by MZSF-20150610-005 --zhangyc 2015.07.22 增加健康通(民生通)支付功能 by MZSF-20150722-001 --zhangyc 2015.07.30 修改账户信息表SF_BRZHXX.TCJE00 by MZSF-20150708-002 --zhangyc 2015.08.13 健康通改造 by MZSF-20150811-001 --zhangyc 2015.09.08 修正血透析减免优惠功能 by MZSF-20150907-005 --zhangyc 2015.09.08 修正打印结算单时 发票序号跳号问题 by MZSF-20150907-006 --zhangyc 2015.12.28 增加ZFFS00=10,11,14 时用YBBHSB=14判断 by MZSF-20151228-002 --zhangyc 2016.01.19 单病种修改 by MZSF-20160115-001 --zhangyc 2016.07.15 vRow.BCBXF0-->LS_HJJE00 by MZSF-20160716-002 --zhangyc 2017.06.22 SP_SF_YFCF00增加执行SP_YF_ZYDZFY、SP_YS_DJPSFZX by MZSF-20170609-002 --zhuyr 2018.05.04 增加未添加的医保预留/说明字段 by MZSF-20180427-002 --yanghq 2018.05.08 增加调用过程SP_JK_INSJKB0 for MZSF-20180509-003 --zhuyr 2018.05.17 增加插入新增发医保预留/说明字段，从YBYL51-YBYL69，YBSM17-YBSM29 by MZSF-20180507-001 --zhuyr 2018.08.06 处理结算新流程中，血透减免计算有误的问题 by MZSF-20180801-004 -- dsm 2019.08.17 调用接口过程增加异常处理 for MZSF-20190817-001 --zhangyc 2019.12.03 新医保接口调用过程SP_SF_NEWYBJK_JKFHJX解析(补助明细、医保发票费用开始) by MZSF-20191203-004 --zhangyc 2019.12.23 新医保接口统筹总额(bkc102:基金支付总额)、医保账户(bkc041:个人账户支付金额)、个人自付(bkc040:个人现金支付金额（实付现金）) 按医保返回为准 by MZSF-20191221-001 --zhangyc 2020.12.10 SF_JZB000保存一些新的字段(FKJSLX,FKLSH0,FKJZDH,FKJZJE,YBBXZE,DLTCJJ,DLZHZF,YBQTZE,DLSYBX,DLJZFP,DLQTJJ,DLQYBC,MZBZJE,DLSBJE,GWBZJE,DLDBJJ,CJBTCE ) by MZSF-20201210-003 --zhangyc 2021.01.14 参数LS_TCJJQZFS=D时bkc062(其中:精准扶贫医疗叠加)、ake173（其中:其他基金支付）ake026（其中:企业补充）根据函数SF_SF_YBFHJE判断累加到统筹基金中 by MZSF-20201229-002 --zhangyc 2021.03.09 修正结账SF_JZB000.TCJJZF=vRow.TCJJZF改为ld_TCJJZF,GRZHZF=vRow.Zhzfe0改为ld_GRZHZF ---------- ------------ ------------------------------------------ as LV_GHID00 SF_FYXX00_TEMP00.GHID00%TYPE; Lv_DJH000 SF_BRFY00.DJH000%TYPE; Lv_DJH001 SF_BRFY00.DJH000%TYPE; lv_KDKS00 SF_BRXXB0.JZKS00%TYPE; lv_KDYS00 SF_BRXXB0.JZYS00%TYPE; lv_ZXKS00 SF_BRXXB0.JZKS00%TYPE; lv_ZXYS00 SF_BRXXB0.JZYS00%TYPE; lv_YSKS00 SF_BRXXB0.JZKS00%TYPE; LS_ZXKS00 YJ_YW0000.YJKSBH%type; --执行科室 LS_YJZXYS YJ_YW0000.KDYS00%type; --开单医生 Lv_CZRQ00 char(8); Lv_CZSJ00 char(8); LS_COUNT0 number(5); LS_SFSCDJ varchar2(1); -- LS_FPXH00 varchar2(30); vRow SF_FYXX00_TEMP00%rowtype; LS_JFDJH0 SF_BRJFB0.DJH000%TYPE; LS_JZDH00 SF_JZB000.JZDH00%TYPE; --结算单号 LS_PJH000 SF_PJSYQK.PJH000%TYPE; --票据流水号 LS_JZYXM0 BM_YGBM00.ZWXM00%TYPE; --结算操作员姓名 LS_CZYKS0 BM_YGBM00.BMBH00%TYPE; LS_JFJE00 number(12,4); LS_YBBHSB IC_YBBRLB.YBBHSB%TYPE; ls_YBMC00 IC_YBBRLB.YBMC00%TYPE; ls_BRXB00 bm_brxxb0.BRXB00%TYPE; ls_BRXM00 bm_brxxb0.BRXM00%TYPE; LS_HJJE00 number(12,4); LS_ZFJE00 number(12,4); LS_JZJE00 number(12,4); LS_GFJE00 number(12,4); LS_BRZJE0 number(12,4); LS_HJJE01 number(12,4); LS_ZFJE01 number(12,4); LS_XJYE00 SF_BRZHXX.XJYE00%TYPE; LS_ZYE000 SF_BRZHXX.ZYE000%TYPE; LS_TYJE00 SF_BRZHXX.TYJE00%TYPE; LS_ZZYE00 SF_BRZHXX.ZZYE00%TYPE; LS_SFDY00 Varchar2(1); LS_DYLX00 Varchar2(1); V_ZYDJFJFBM BM_YYSFXM.SFXMID%TYPE;--中药煎药费附加费收费代码 V_JYSFDM BM_YYSFXM.SFXMID%TYPE; --中药煎药费收费代码 V_JYSFDM_SC BM_YYSFXM.SFXMID%TYPE; --中药煎药费收费代码(每日三次) V_JYSFDM_FIRST BM_YYSFXM.SFXMID%TYPE; --中药煎药费收费代码 (第一帖的收费标准) LS_JZBJE0 number(12,4); --结账表金额 LS_FYBJE0 number(12,4); --结账表金额 LS_TEMPJE number(12,4); --临时表数据 LS_SFJM00 varchar2(10); --是否减免 LS_JMJE00 number(12,4); --血透析减免金额 LS_JMDJH0 number(10); V_MsgStr varchar2(100); V_ZYDJDH number(10); --返回中药代煎单号 V_ZYDZDH number(10); --返回中药袋子费用单号 V_ZYPSDH number(10); --返回配送费用单号 V_MZCF00 YF_MZCF00%rowtype; V_YJYW00 YJ_YW0000%rowtype; LS_FSWBJKSJ varchar2(30); LS_ZXZT00 number(10);--输出:执行状态 0:失败 1：成功 LS_ERRMSG varchar2(400);--输出:错误信息 LS_YBJKID varchar2(20);--医保接口ID LS_akc227 number(12,2);--akc227 医疗费总金额=个人现金支付金额+个人账户支付金额+基金支付总额 LS_bkc102 number(12,2);--bkc102 基金支付总额=统筹基金支付+商保基金支付+公务员医疗补助+精准扶贫医疗叠加+医疗救助基金+其他基金支付+企业补充 LS_bkc040 number(12,2);--bkc040 个人现金支付金额（实付现金） LS_bkc041 number(12,2);--bkc041 个人账户支付金额 LS_bkc045 number(12,2);--bkc045 其中：统筹基金支付 LS_bkc059 number(12,2);--bkc059 其中：公务员医疗补助(bkc059=bkc059_1+bkc059_2+bkc059_3) LS_bkc052 number(12,2);--bkc052 其中：商保基金支付（大额补充） LS_bkc062 number(12,2);--bkc062 其中：精准扶贫医疗叠加 LS_bkc060 number(12,2);--bkc060 其中：医疗救助基金 LS_ake173 number(12,2);--ake173 其中：其他基金支付 LS_ake026 number(12,2);--ake026 其中：企业补充 LS_bkc011 number(12,2);--bkc011 个人自费（非医保费用） LS_akc087 number(12,2);--akc087 个人账户余额 LS_aka151 number(12,2);--aka151 统筹支付医保费用起付标准 LS_aka150 number(12,2);--aka150 本年度住院次数 LS_bkc612 number(12,2);--bkc612 补助基金支付bkc612=bkc052 大病保险基金支付+bkc059公务员医疗补助+bkc062精准扶贫医疗叠加+bkc060医疗救助基金+ake173 其他基金支付+ake026企业补充+bkc061保健基金支付 LS_bkc064 number(12,2);--产前检查费(医保返回) LS_FYJSCE number(12,2);--费用结算差额 BM_bkc001_mc BM_BRXXB0.bkc001_mc%type;--人员待遇类别名称(城乡居民、普通在职人员等等) BM_aae140 BM_BRXXB0.aae140%type;--险种类型编码:310 城镇职工基本医疗保险 320:公务员医疗补助 380:新型农村合作医疗 390:城乡居民基本医疗保险 410:工伤保险 510:生育保险 520:机关生育 BM_aae140_mc BM_BRXXB0.aae140_mc%type;--险种类型名称:城乡居民基本医疗保险、城镇职工基本医疗保险等等 LS_bkc001_mc SF_JZB000.bkc001_mc%type;--人员待遇类别名称(城乡居民、普通在职人员等等) LS_aae140 SF_JZB000.aae140%type;--险种类型编码:310 城镇职工基本医疗保险 320:公务员医疗补助 380:新型农村合作医疗 390:城乡居民基本医疗保险 410:工伤保险 510:生育保险 520:机关生育 LS_aae140_mc SF_JZB000.aae140_mc%type;--险种类型名称:城乡居民基本医疗保险、城镇职工基本医疗保险等等 LS_bke174 SF_JZB000.bke174%type;--异地就医类型编码:1 省内 2 省外 3 市内 4 市外 LS_bke174_mc SF_JZB000.bke174_mc%type;--异地就医类型名称:(省内、省外、市内、市外) LS_JSQYE0 SF_JZB000.JSQYE0%type;--结算前账户余额 LS_JSHYE0 SF_JZB000.JSQYE0%type;--结算后账户余额 ld_TCJJZF number(12,2);--统筹基金 ld_GRZHZF number(12,2);--医保账户支付 ld_SBTJJE number(12,2);--商保统筹基金支付金额 ld_SBZHJE number(12,2);--商保帐户支付金额 ld_JJZFE0 number(12,2);--保健基金支付金额 ld_SYBXZF number(12,2);--商业保险支付金额 ld_SBZFE0 number(12,2);--商保基金支付(省属三家市属九家医疗机构) ld_BJZFE0 number(12,2);--保健基金支付(省属三家市属九家医疗机构) ld_GWYBZ0 number(12,2);--公务员补助 LS_FKJSLX SF_JZB000.FKJSLX%type;--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0 SF_JZB000.FKLSH0%type;--分开结算流水号 LS_FKJZDH SF_JZB000.FKJZDH%type;--分开结算单号 LS_FKJZJE SF_JZB000.FKJZJE%type;--分开结算金额 LS_YBBXZE SF_JZB000.YBBXZE%type;--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_DLTCJJ SF_JZB000.DLTCJJ%type;--实际医保统筹基金 LS_DLZHZF SF_JZB000.DLZHZF%type;--实际医保账户支付 LS_YBQTZE SF_JZB000.YBQTZE%type;--医保其它总额 LS_DLSYBX SF_JZB000.DLSYBX%type;--商业保险支付(独立于统筹基金金额) LS_DLJZFP SF_JZB000.DLJZFP%type;--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ SF_JZB000.DLQTJJ%type;--其他基金支付(独立于统筹基金金额) LS_DLQYBC SF_JZB000.DLQYBC%type;--企业补充(独立于统筹基金金额) LS_DLMZBZ SF_JZB000.MZBZJE%type;--民政补助金额(独立于统筹基金金额) LS_DLSBJE SF_JZB000.DLSBJE%type;--商保支付金额(独立于统筹基金金额) LS_GWBZJE SF_JZB000.GWBZJE%type;--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ SF_JZB000.DLDBJJ%type;--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE SF_JZB000.CJBTCE%type;--产检检查费（bkc064）超出个人自付金额后需补退的差额 LS_TCJJQZFS varchar2(10);--新医保接口统筹基金取值方式 Y:取基金总额:bkc102,N:取其中基金支付:bkc045和补偿总额bkc612合并到bkc102 D:bkc045,bkc059,bkc052,bkc060独立写入 默认:Y LS_SFXMYBJK varchar2(10);--是否厦门医保接口 Y:是 N:否 LD_MZBZJE SF_JZB000.YBYL18%type;--民政补助金额 CURSOR CUR_WJZXM0 IS select distinct CFLSH0,YJDJH0,XMFL00 From SF_FYXX00_DJH000 where ID0000=PID0000 and BRID00=PBRID00 and KFZT00='0' and JZDH00=0 and ((XMFL00='0' and CFID00>0) or (XMFL00='1' and YJDJH0>0)) order by XMFL00, CFLSH0,YJDJH0; begin LS_SFDY00:=substrb(PSFDYFP,1,1); --是否打印 Y打印 N 不打印 LS_DYLX00:=substrb(PSFDYFP,3,1); --打印类型 N打印发票 Y打印结算单 M不打印发票和结算单 PJZDH00:=0; LS_JMJE00:=0; Lv_CZRQ00:=to_char(sysdate,'YYYYMMDD'); Lv_CZSJ00:=to_char(sysdate,'HH24:MI:SS'); LS_FPXH00:=PFPXH00; ld_TCJJZF:=0;--统筹基金 ld_GRZHZF:=0;--医保账户支付 ld_SBTJJE:=0;--商保统筹基金支付金额 ld_SBZHJE:=0;--商保帐户支付金额 ld_JJZFE0:=0;--保健基金支付金额 ld_SYBXZF:=0;--商业保险支付金额 ld_SBZFE0:=0;--商保基金支付(省属三家市属九家医疗机构) ld_BJZFE0:=0;--保健基金支付(省属三家市属九家医疗机构) ld_GWYBZ0:=0;--公务员补助 LS_FKJSLX:='ZFJS';--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0:=null;--分开结算流水号 LS_FKJZDH:=null;--分开结算单号 LS_FKJZJE:=null;--分开结算金额 LS_YBBXZE:=0;--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_DLTCJJ:=0;--实际医保统筹基金 LS_DLZHZF:=0;--实际医保账户支付 LS_YBQTZE:=0;--医保其它总额 LS_DLSYBX:=0;--商业保险支付(独立于统筹基金金额) LS_DLJZFP:=0;--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ:=0;--其他基金支付(独立于统筹基金金额) LS_DLQYBC:=0;--企业补充(独立于统筹基金金额) LS_DLMZBZ:=0;--民政补助金额(独立于统筹基金金额) LS_DLSBJE:=0;--商保支付金额(独立于统筹基金金额) LS_GWBZJE:=0;--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ:=0;--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE:=0;--产检检查费（bkc064）超出个人自付金额后需补退的差额 Select ZWXM00,BMBH00 into LS_JZYXM0,LS_CZYKS0 from BM_YGBM00 where YGBH00=PCZY000; select count(1) into LS_COUNT0 from SF_FYXX00_DJH000 where BRID00=PBRID00 and ID0000=PID0000 and JZDH00=0; if LS_COUNT0=0 then PYHMSG0:='结算错误:未找到有效的数据'; PSYSMSG:= '未找到有效的数据:Select * from SF_FYXX00_DJH000 Where JZDH00=0 and ID0000='||PID0000; return; end if; --获取结算后账户总余额 (参数：0：门诊 1：住院 参数2：BRID00 参数3：1：门诊 2:住院 参数4：0:可用余额 1:总余额 2：现金余额 3:停用金额) begin select SF_SF_GETBRZHYE(0,PBRID00,1,1) into LS_JSQYE0 from dual; exception when others then LS_JSQYE0:=0; end; --是否厦门医保接口 Y:是 N:否 begin select substrb(trim(value0),1,10) into LS_SFXMYBJK from XT_XTCS00 where name00='SF_SFXMYBJK'; exception when others then LS_SFXMYBJK:='N'; end; LS_TCJJQZFS:=nvl(substrb(SF_SF_TYZD00('医保新接口参数设置','TCJJQZFS'),1,10),'Y'); --获取单据号 select * into vRow from SF_FYXX00_TEMP00 where BRID00=PBRID00 and ID0000=PID0000; LV_GHID00:=vRow.GHID00; LS_YBJKID:=substrb(nvl(vRow.YBJKID,vRow.YBSM19),1,20);--医保接口ID LS_akc227:=nvl(vRow.YBYL52,0);--akc227:医疗费总金额=个人现金支付金额+个人账户支付金额+基金支付总额 LS_bkc102:=nvl(vRow.YBYL53,0);--bkc102:基金支付总额=统筹基金支付+商保基金支付+公务员医疗补助+精准扶贫医疗叠加+医疗救助基金+其他基金支付+企业补充 LS_bkc040:=nvl(vRow.YBYL54,0);--bkc040:个人现金支付金额（实付现金） LS_bkc041:=nvl(vRow.YBYL55,0);--bkc041:个人账户支付金额 LS_bkc045:=nvl(vRow.YBYL56,0);--bkc045:其中：统筹基金支付 LS_bkc059:=nvl(vRow.YBYL57,0);--bkc059:其中：公务员医疗补助(bkc059=bkc059_1+bkc059_2+bkc059_3) LS_bkc052:=nvl(vRow.YBYL58,0);--bkc052:其中：商保基金支付（大额补充） LS_bkc062:=nvl(vRow.YBYL59,0);--bkc062:其中：精准扶贫医疗叠加 LS_bkc060:=nvl(vRow.YBYL60,0);--bkc060:其中：医疗救助基金 LS_ake173:=nvl(vRow.YBYL61,0);--ake173:其中：其他基金支付 LS_ake026:=nvl(vRow.YBYL62,0);--ake026:其中：企业补充 LS_bkc011:=nvl(vRow.YBYL63,0);--bkc011:个人自费（非医保费用） LS_akc087:=nvl(vRow.YBYL64,0);--akc087:个人账户余额 LS_aka151:=nvl(vRow.YBYL65,0);--aka151:统筹支付医保费用起付标准 LS_aka150:=nvl(vRow.YBYL66,0);--aka150:本年度住院次数 LS_bkc612:=nvl(vRow.YBYL67,0);--bkc612:补助基金支付bkc612=bkc052 大病保险基金支付+bkc059公务员医疗补助+bkc062精准扶贫医疗叠加+bkc060医疗救助基金+ake173 其他基金支付+ake026企业补充+bkc061保健基金支付 LS_bkc064:=nvl(vRow.YBYL68,0);--bkc064:产前检查费(医保返回) LS_FYJSCE:=0;--费用结算差额 Select JZKS00,JZYS00 into lv_KDKS00,lv_KDYS00 from SF_BRXXB0 where BRID00=PBRID00 and GHID00=LV_GHID00; lv_ZXKS00:=lv_KDKS00; lv_ZXYS00:=lv_KDYS00; lv_YSKS00:=lv_KDKS00; Select BRXB00,BRXM00,BKC001_MC,AAE140, AAE140_MC into ls_BRXB00,ls_BRXM00,BM_BKC001_MC,BM_AAE140,BM_AAE140_MC from bm_brxxb0 where BRID00=PBRID00; Select YBBHSB,YBMC00 into LS_YBBHSB,LS_YBMC00 from IC_YBBRLB where FBBH00=vRow.FBBH00 and YBLB00=vRow.YBLB00; select SQ_SF_JZB000_JZDH00.nextval into LS_JZDH00 from dual; select SQ_SF_PJSYQK_PJH000.nextval into LS_PJH000 from dual; pJZDH00:=LS_JZDH00; V_MsgStr:='插入缴费表数据'; if vRow.YBJKLX not in ('-1') then --结算方式 -1:自费 0医保 1生育 2:工伤 3:保健 LS_FKJSLX:='YBJS';--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 if LS_YBJKID is not null and LS_YBJKID not in ('0') then --新接口--begin-- ld_TCJJZF:=nvl(LS_bkc102,0);--统筹基金 ld_GRZHZF:=nvl(LS_bkc041,0);--医保账户支付 ld_SBTJJE:=0;--商保统筹基金支付金额 ld_SBZHJE:=0;--商保帐户支付金额 ld_JJZFE0:=0;--保健基金支付金额 ld_SYBXZF:=0;--商业保险支付金额 ld_SBZFE0:=0;--商保基金支付(省属三家市属九家医疗机构) ld_BJZFE0:=0;--保健基金支付(省属三家市属九家医疗机构) ld_GWYBZ0:=0;--公务员补助 if LS_TCJJQZFS in ('D') then --统筹基金、公务员补助、商业保险支付、医疗救助基金 各个项目独立生成 ld_TCJJZF:=nvl(LS_bkc045,0); --YBYL56 统筹基金(bkc045,jjzfe0) --（ake173:其中：其他基金支付 +ake026:其中：企业补充+bkc062:其中：精准扶贫医疗叠加 没有独立支付方式直接累加到统筹中） ld_TCJJZF:=nvl(ld_TCJJZF,0) +SF_SF_YBFHJE(PBRID00,'','YBYL61',nvl(LS_ake173,0)) --YBYL61=ake173其中:其他基金支付 +SF_SF_YBFHJE(PBRID00,'','YBYL62',nvl(LS_ake026,0)) --YBYL62=ake026其中:企业补充 +SF_SF_YBFHJE(PBRID00,'','YBYL59',nvl(LS_bkc062,0)) --YBYL59=其中:精准扶贫医疗叠加 ; ld_GRZHZF:=nvl(LS_bkc041,0); --YBYL55 医保账户支付bkc041（个人账户支付总额）=bkc077（帐户支付）+bkc076（本人健康帐户支付）+bkc075（家庭共济帐户支付） ld_GWYBZ0:=SF_SF_YBFHJE(PBRID00,'','YBYL57',nvl(LS_bkc059,0)); --YBYL57 公务员补助(bkc059,gwybz0) if LS_SFXMYBJK='Y' then --厦门医保bkc052=sybxzf ld_SYBXZF:=SF_SF_YBFHJE(PBRID00,'','YBYL58',nvl(LS_bkc052,0)); --YBYL58 商业保险支付bkc052,sybxzf（大病保险基金支付） else ld_SBTJJE:=SF_SF_YBFHJE(PBRID00,'','YBYL58',nvl(LS_bkc052,0)); --YBYL58 其中：商保基金支付（大额补充）bkc052 end if; LD_MZBZJE:=SF_SF_YBFHJE(PBRID00,'','YBYL60',nvl(LS_bkc060,0)); --YBYL60 医疗救助基金(bkc060、mzbzje） end if; LS_BKC001_MC:=substrb(SF_SF_GETYBJKMX(LS_YBJKID,'bkc001_mc','1'),1,50);--bkc001_mc:人员待遇类别名称(城乡居民、普通在职人员等等) LS_AAE140:=substrb(SF_SF_GETYBJKMX(LS_YBJKID,'aae140','1'),1,10); --aae140:险种类型编码:310 城镇职工基本医疗保险 320:公务员医疗补助 380:新型农村合作医疗 390:城乡居民基本医疗保险 410:工伤保险 510:生育保险 520:机关生育 LS_AAE140_MC:=substrb(SF_SF_GETYBJKMX(LS_YBJKID,'aae140_mc','1'),1,50);--aae140_mc:险种类型名称:城乡居民基本医疗保险、城镇职工基本医疗保险等等 LS_BKE174:=substrb(SF_SF_GETYBJKMX(LS_YBJKID,'bke174','1'),1,10); --bke174:异地就医类型编码:1 省内 2 省外 3 市内 4 市外 LS_BKE174_MC:=substrb(SF_SF_GETYBJKMX(LS_YBJKID,'bke174_mc','1'),1,50);--bke174_mc:异地就医类型名称:(省内、省外、市内、市外) else ld_TCJJZF:=nvl(vRow.TCJJZF,0);--统筹基金 ld_GRZHZF:=nvl(vRow.GRZHZF,0);--医保账户支付 ld_SBTJJE:=nvl(vRow.SBTCJJ,0);--商保统筹基金支付金额 ld_SBZHJE:=nvl(vRow.SBGRZH,0);--商保帐户支付金额 ld_JJZFE0:=nvl(vRow.jjzfe0,0);--保健基金支付金额 ld_SYBXZF:=nvl(vRow.SYBXZF,0);--商业保险支付金额 ld_SBZFE0:=nvl(vRow.SBZFE0,0);--商保基金支付(省属三家市属九家医疗机构) ld_BJZFE0:=nvl(vRow.BJZFE0,0);--保健基金支付(省属三家市属九家医疗机构) ld_GWYBZ0:=nvl(vRow.GWYBZ0,0);--公务员补助 end if; V_MsgStr:='插入缴费表数据医保个人账户=zhzfe0'; select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) values(LS_JFDJH0,PBRID00,1,ld_GRZHZF,3,'医保个人账户','',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_GRZHZF,LS_CZYKS0); V_MsgStr:='插入缴费表数据医保统筹基金=jjzfe0'; select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) values(LS_JFDJH0,PBRID00,1,ld_TCJJZF,4,'医保统筹基金','',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_TCJJZF,LS_CZYKS0); /* V_MsgStr:='插入缴费表数据商保账户支付=dbzhzf'; select decode(sign(Instrb(','||trim(LS_YBBHSB)||',',',14,')),1,0,ld_SBZHJE) into ld_SBZHJE from dual; if ld_SBZHJE>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,ld_SBZHJE,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_SBZHJE,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=10; end if; */ V_MsgStr:='插入缴费表数据商保统筹金额=dbjjzf'; select decode(sign(Instrb(','||trim(LS_YBBHSB)||',',',14,')),1,0,ld_SBTJJE) into ld_SBTJJE from dual; if ld_SBTJJE>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,ld_SBTJJE,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_SBTJJE,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=11; end if; V_MsgStr:='插入缴费表数据保健基金支付'; if ld_JJZFE0>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,ld_JJZFE0,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_JJZFE0,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=6; end if; V_MsgStr:='插入缴费表数据商业保险支付=sybxzf'; if ld_SYBXZF>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,ld_SYBXZF,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_SYBXZF,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=13; end if; V_MsgStr:='插入缴费表数据商保支付=sbzfe0'; select decode(sign(Instrb(','||trim(LS_YBBHSB)||',',',14,')),1,0,ld_SBZFE0) into ld_SBZFE0 from dual; if ld_SBZFE0>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,ld_SBZFE0,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_SBZFE0,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=14; end if; V_MsgStr:='插入缴费表数据保健支付'; if ld_BJZFE0>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,ld_BJZFE0,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_BJZFE0,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=15; end if; V_MsgStr:='插入缴费表数据公务员补助'; select decode(sign(Instrb(','||trim(LS_YBBHSB)||',',',15,')),1,0,ld_GWYBZ0) into ld_GWYBZ0 from dual; if ld_GWYBZ0>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,ld_GWYBZ0,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,ld_GWYBZ0,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=16; end if; V_MsgStr:='插入医院支付'; LS_JFJE00:=nvl(vRow.YBYL05,0); if LS_JFJE00>0 then select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,LS_JFJE00,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,LS_JFJE00,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=20; end if; LS_DLTCJJ:=nvl(ld_TCJJZF,0);--实际医保统筹基金 LS_DLZHZF:=nvl(ld_GRZHZF,0);--实际医保账户支付 LS_YBQTZE:=0;--医保其它总额 LS_DLSYBX:=nvl(ld_SYBXZF,0);--商业保险支付(独立于统筹基金金额) LS_DLJZFP:=0;--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ:=nvl(ld_JJZFE0,0)+nvl(ld_BJZFE0,0)+nvl(ld_SBZFE0,0);--其他基金支付(独立于统筹基金金额) LS_DLQYBC:=0;--企业补充(独立于统筹基金金额) LS_DLMZBZ:=0;--民政补助金额(独立于统筹基金金额) LS_DLSBJE:=nvl(ld_SBTJJE,0);--商保支付金额(独立于统筹基金金额) LS_GWBZJE:=nvl(ld_GWYBZ0,0);--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ:=0;--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE:=0;--产检检查费（bkc064）超出个人自付金额后需补退的差额 --医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_YBBXZE:=nvl(LS_DLTCJJ,0)+nvl(LS_DLZHZF,0)+nvl(LS_YBQTZE,0)+nvl(LS_DLSYBX,0)+nvl(LS_DLJZFP,0)+nvl(LS_DLQTJJ,0)+nvl(LS_DLQYBC,0)+nvl(LS_DLMZBZ,0)+nvl(LS_DLSBJE,0)+nvl(LS_GWBZJE,0)+nvl(LS_DLDBJJ,0); end if; V_MsgStr:='执行处方记账或医技记账'; LS_SFSCDJ := '1'; select SF_SF_TYZD00('发送外部接口数据','Open') into LS_FSWBJKSJ from dual; for R_WJZ in CUR_WJZXM0 loop if (LS_SFSCDJ= '1') then LS_SFSCDJ := '0'; select SQ_SF_BRJFB0_DJH000.nextval into Lv_DJH000 from dual; end if; if (R_WJZ.XMFL00='0') then --处方表 V_MsgStr:='执行处方记账或医技记账:SP_SF_YFCF00'; SP_SF_YFCF00(R_WJZ.CFLSH0,Lv_DJH000,0,0,0,'N',PYHMSG0,PSYSMSG,PCZY000,'N'); if trim(PSYSMSG) is null then update SF_FYXX00_DJH000 set KFZT00='1',DJH000=Lv_DJH000 where BRID00=PBRID00 and ID0000=PID0000 and CFLSH0=R_WJZ.CFLSH0; else PYHMSG0:='执行SP_SF_YFCF00错误:'||PYHMSG0; PSYSMSG:=PSYSMSG; return; end if; --暂时不执行以下代码,因为中间表SF_FYXX00_TEMPMX 已经有数据了 --/* --执行SP_YF_ZYDZFY、SP_YS_DJPSFZX by MZSF-20170609-002 (备注：单独执行，不在过程SP_SF_YFCF00中执行) V_MsgStr:='执行处方中药代煎和附加费:SP_SF_ZYDJFY'; SP_SF_ZYDJFY(R_WJZ.CFLSH0,PCZY000,V_ZYDJDH,PYHMSG0,PSYSMSG); if trim(PSYSMSG) is null then --把单号插入到临时表 insert into SF_FYXX00_DJH000 (ID0000,BRID00,GHID00,GHH000,DJH000,XMFL00,KFZT00,YPNM00,SFXMID )values (PID0000,VROW.BRID00,VROW.GHID00,VROW.GHH000,V_ZYDJDH,'3','1',0,0 ); else PYHMSG0:='执行SP_SF_ZYDJFY错误:'||PYHMSG0; PSYSMSG:=PSYSMSG; return; end if; V_MsgStr:='执行处方中药袋子费用:SP_YF_ZYDZFY'; SP_YF_ZYDZFY(R_WJZ.CFLSH0,PCZY000,V_ZYDZDH,PYHMSG0,PSYSMSG);--中药袋子费用 if trim(PSYSMSG) is null then --把单号插入到临时表 insert into SF_FYXX00_DJH000 (ID0000,BRID00,GHID00,GHH000,DJH000,XMFL00,KFZT00,YPNM00,SFXMID )values (PID0000,VROW.BRID00,VROW.GHID00,VROW.GHH000,V_ZYDZDH,'3','1',0,0 ); else PYHMSG0:='执行SP_YF_ZYDZFY错误:'||PYHMSG0; PSYSMSG:=PSYSMSG; return; end if; V_MsgStr:='执行处方配送费收取:SP_YS_DJPSFZX'; SP_YS_DJPSFZX(R_WJZ.CFLSH0,PCZY000,V_ZYPSDH,PYHMSG0,PSYSMSG);--配送费收取 if trim(PSYSMSG) is null then --把单号插入到临时表 insert into SF_FYXX00_DJH000 (ID0000,BRID00,GHID00,GHH000,DJH000,XMFL00,KFZT00,YPNM00,SFXMID )values (PID0000,VROW.BRID00,VROW.GHID00,VROW.GHH000,V_ZYPSDH,'3','1',0,0 ); else PYHMSG0:='执行SP_YS_DJPSFZX错误:'||PYHMSG0; PSYSMSG:=PSYSMSG; return; end if; --*/ --修改中药代煎单据号 if nvl(V_ZYDJDH,0)>0 then update YF_MZCF00 set FJFDJH=V_ZYDJDH where BRID00=vRow.BRID00 and GHID00=vRow.GHID00 and CFLSH0=R_WJZ.CFLSH0 and ZYDJS0>0; end if; end if; if (R_WJZ.XMFL00= '1') then --医技表 V_MsgStr:='执行处方记账或医技记账:SP_SF_YJDJ00'; select YJKSBH,KDYS00 into LS_ZXKS00,LS_YJZXYS from yj_yw0000 where YJDJH0 =R_WJZ.YJDJH0 and BRID00=PBRID00; SP_SF_YJDJ00(R_WJZ.YJDJH0,Lv_DJH000,ls_ZXKS00,ls_YJZXYS,PYHMSG0,PSYSMSG); if trim(PSYSMSG) is null then update SF_FYXX00_DJH000 set KFZT00='1',DJH000=Lv_DJH000 where BRID00=PBRID00 and ID0000=PID0000 and YJDJH0=R_WJZ.YJDJH0 ; else PYHMSG0:='执行SP_SF_YJDJ00错误:'||PYHMSG0; PSYSMSG:=PSYSMSG; return; end if; end if; if LS_FSWBJKSJ='Y' then if R_WJZ.XMFL00='0' then select * into V_MZCF00 from YF_MZCF00 where CFLSH0=R_WJZ.CFLSH0; --SP_JK_INSJKB0('0',V_MZCF00.GHID00,V_MZCF00.BRID00,V_MZCF00.CFLSH0,V_MZCF00.CFZT00,V_MZCF00.DJH000,null,'0',null,PCZY000,V_MZCF00.YFBMBH,null,V_MZCF00.BCCFH0,'1'); begin execute immediate 'begin SP_JK_INSJKB0(''0'','||V_MZCF00.GHID00||','||V_MZCF00.BRID00||','''||V_MZCF00.CFLSH0||''','||V_MZCF00.CFZT00||','||V_MZCF00.DJH000||',null,''0'',null,'||nvl(PCZY000,'0')||','||V_MZCF00.YFBMBH||',null,'''||V_MZCF00.BCCFH0||''',''1''); end; '; exception when others then LS_COUNT0:=0; end; elsif R_WJZ.XMFL00='1' then select * into V_YJYW00 from YJ_YW0000 where YJDJH0=R_WJZ.YJDJH0; --SP_JK_INSJKB0('0',null,V_YJYW00.BRID00,null,null,V_YJYW00.SFDJH0,V_YJYW00.YJDJH0,'1',V_YJYW00.XMZT00,PCZY000,V_YJYW00.YJKSBH,0,null); begin execute immediate 'begin SP_JK_INSJKB0('||'0'||','||V_YJYW00.ZYGHID||','||V_YJYW00.BRID00||',null,null,'||V_YJYW00.SFDJH0||','||V_YJYW00.YJDJH0||',''1'','||V_YJYW00.XMZT00||','||nvl(PCZY000,'0')||','||V_YJYW00.YJKSBH||',0,null); end; '; exception when others then LS_COUNT0:=0; end; end if; end if; end loop; /* Insert into SF_FYMX00 (MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00, HJJE00,ZFJE00,GFJE00,JZJE00,CZRQ00,CZSJ00,ZXRQ00,ZXSJ00, KDKS00,KDYS00,ZXKS00,ZXYS00,SFYDJ0,YSKS00,ZFBL00,QZFJE0 ) select SQ_SF_FYMX00_MXID00.nextval,Lv_DJH000,SFXMID,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00, xmje00,ZFJE00,GFJE00,JZJE00,Lv_CZRQ00,Lv_CZSJ00,Lv_CZRQ00,Lv_CZSJ00, lv_KDKS00,lv_KDYS00,lv_ZXKS00,lv_ZXYS00,'1',lv_YSKS00,ZFBL00,0 from SF_FYXX00_TEMPMX where BRID00=PBRID00 AND ID0000=PID0000 and KFZT00='0' and YJDJH0=0 and CFID00=0; */ V_MsgStr:='执行过程SP_SF_BRFY00_REG000开始'; if (LS_SFSCDJ='0') then SP_SF_BRFY00_REG000( as_YBDJH0=>vRow.YBDJH0, as_MZH000=>vRow.GHH000, ad_MZID00=>vRow.GHID00, as_JFLBMC=>'门诊预交金', ad_BRID00=>vRow.BRID00, as_XM0000=>vRow.BRXM00, ad_BRFB00=>vRow.FBBH00, ad_CZY000=>PCZY000, as_CZYXM0=>LS_JZYXM0, ad_CZYKS0=>LS_CZYKS0, ad_DJH000=>LV_DJH000, ad_HJJE00=>LS_HJJE00, ad_ZFJE00=>LS_ZFJE00, ad_JZJE00=>LS_JZJE00, ad_GFJE00=>LS_GFJE00, as_YHMSG0=>PYHMSG0, as_SYSMSG=>PSYSMSG ); if trim(PSYSMSG) is not null then PYHMSG0:='执行SP_SF_BRFY00_REG000错误:'||PYHMSG0; PSYSMSG:=PSYSMSG; return; end if; end if; V_MsgStr:='把单号插入到临时表SF_FYXX00_DJH000'; insert into SF_FYXX00_DJH000 (ID0000,BRID00,GHID00,GHH000,DJH000,XMFL00,KFZT00,YPNM00,SFXMID )values (PID0000,vRow.BRID00,vRow.GHID00,vRow.GHH000,LV_DJH000,'3','1',0,0 ); --/* V_MsgStr:='执行血透析减免--begin---'; select SF_SF_TSBZJM(PID0000,vRow.BRID00,vRow.TSBZBH,vRow.GHH000,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'YYYYMMDD')) into LS_SFJM00 from dual; if LS_SFJM00='Y' then SP_SF_FYXX00_TSBZJM ( p_ID0000=>PID0000, P_BRID00=>vRow.BRID00,--病人id P_CZY000=>pCZY000,--操作员 P_JZDH00=>0, P_GHH000=>vRow.GHH000, --挂号号 P_KSRQ00=>to_char(sysdate-7,'YYYYMMDD'),--开始日期 P_JSRQ00=>to_char(sysdate,'YYYYMMDD'),--结束日期 P_HJJE00=>vRow.HJJE00, --可减免总金额 P_JMBZ00=>'5', --1:减免 2:优惠 3： 4：事前减免 5:病种减免 P_TSBZBH=>vRow.TSBZBH, P_JMJE00=>LS_JMJE00, P_YHMSG0=>PYHMSG0, --存储过程提示的错误信息 P_SYSMSG=>PSYSMSG, P_ZFJE00=>LS_ZFJE00 ); if trim(PSYSMSG) is not null then PYHMSG0:='执行SF_SF_TSBZJM错误:'||PYHMSG0; PSYSMSG:=PSYSMSG; return; else if nvl(LS_JMJE00,0)>0 then --医院支付 select SQ_SF_BRJFB0_DJH000.nextval into LS_JFDJH0 from dual ; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0) select LS_JFDJH0,PBRID00,1,LS_JMJE00,ZFFSBH,ZFFSMC,'',LS_PJH000, Lv_CZRQ00,Lv_CZSJ00,PCZY000,ls_JZYXM0,0,'0',0,LS_JZDH00,1,LS_JMJE00,LS_CZYKS0 from BM_BRZFFS where ZFFSBH=20; vRow.YBYL05 := LS_JMJE00; --通过计算有减免金额时，更新vRow.YBYL05 end if; end if; end if; V_MsgStr:='执行血透析减免--end---'; V_MsgStr:='插入挂号和诊查费在医技业务表中导致没有记录到单据号'; insert into SF_FYXX00_DJH000 (ID0000,BRID00,GHID00,GHH000,DJH000,XMFL00,KFZT00,YPNM00,SFXMID) select PID0000,vRow.BRID00,vRow.GHID00,vRow.GHH000,SFDJH0,'1','1',0,0 from YJ_YW0000 where BRID00=vRow.BRID00 and ZYGHID=vRow.GHID00 and SFDJH0>0 and FJDJBZ in ('3','4'); V_MsgStr:='修改费用表的结账单号'; Update SF_BRFY00 D set YBDJH0 =vRow.YBDJH0, --医保的单据流水号 JZDH00 = LS_JZDH00 --结算单号 where D.JZDH00+0= 0 --未结算的病人 and d.MZID00=vRow.GHID00 and d.MZH000=vRow.GHH000 and d.DJH000 in (select DJH000 from SF_FYXX00_DJH000 where BRID00 = PBRID00 and ID0000=PID0000 and DJH000<>0) and D.JFLBID = 1 --有门诊预交金账户的病人 and D.BRID00 = PBRID00; select nvl(sum(hjje00),0),nvl(sum(zfje00),0),nvl(sum(gfje00),0),nvl(sum(jzje00),0) into ls_HJJE00,LS_ZFJE00,LS_GFJE00,LS_JZJE00 from SF_BRFY00 D where D.JZDH00=LS_JZDH00 and D.BRID00 = pBRID00; select SF_SF_YBFYJS( LS_HJJE00, --费用总额 --by MZSF-20160716-002 --vRow.BCBXF0, vRow.TCJJZF, --统筹基金 vRow.GRZHZF, --医保账户支付 vRow.SBGRZH, --担保账户支付 vRow.SBTCJJ, --担保基金 vRow.JJZFE0, --保健 vRow.GWYBZ0, --公务员补助 0, vRow.grzfe0, --个人支付金额 vRow.BRID00, --病人ID vRow.YBYL01, --bcyljz 民政救助 vRow.YBYL02, --jsjzze 计生救助 vRow.YBYL03, --cljzze 残联救助 vRow.YBYL04, --dbzxebz 单病种限额 vRow.YBYL05, --医院支付(医保无返回) vRow.YBYL06, --ybjjzf低保基金支付 vRow.YBYL07, --ybzlfbcje一般诊疗费补偿金额 vRow.YBYL08, --ybzlfgrzfje一般诊疗费费个人支付金额 vRow.YBYL09, --tmqflj-特殊门诊起付线累计 vRow.YBYL10, --tmjjlj特殊门诊基金累计 vRow.YBYL11, --Jkzhzf健康账户支付 vRow.YBYL12, --Jkzhye健康账户余额 vRow.YBYL13, --GJZHZF共济账户支付 vRow.SYBXZF, --目前就厦门使用 '1',--标志 1返回自付金额 2是医保支付总额 '1', --是否预结算标志 0预结算 1结算 vRow.YBYL14, --Gjzhye共济账户余额 vRow.YBYL15, --Jkzfyb健康账户支付医保费用 vRow.YBYL16, --Jkzffyb健康账户支付非医保费用 vRow.YBYL17, --医保减免金额 vRow.YBYL18, --民政(医疗救助基金)补助金额 vRow.YBYL19, --超医保上限金额(非医保返回) vRow.YBYL20, --个人账户支付自付 vRow.YBYL21, --个人现金支付自付 vRow.YBYL22, --血费报销金额(血费系统) vRow.YBYL23, --医保结算金额误差（总费用-定额） vRow.YBYL24, --大额医疗费用报销金额 vRow.YBYL25, --单病种补偿金额(定额-总费用) vRow.YBYL26, --大病补充补偿金额 vRow.YBYL27, --保健医保统筹支付(已包含在统筹中) vRow.YBYL28, --择日住院门诊总费用 vRow.YBYL29, --择日住院门诊范围内费用 vRow.YBYL30, --择日住院门诊统筹(医保未返回) nvl(LS_HJJE00,0), --费用总额 nvl(vRow.mxzje0,0), nvl(vRow.YBYL31,0), nvl(vRow.YBYL32,0), nvl(vRow.YBYL33,0), nvl(vRow.YBYL34,0), nvl(vRow.YBYL35,0), nvl(vRow.YBYL36,0), nvl(vRow.YBYL37,0), nvl(vRow.YBYL38,0), nvl(vRow.YBYL39,0), nvl(vRow.YBYL40,0), nvl(vRow.YBYL41,0), nvl(vRow.YBYL42,0), nvl(vRow.YBYL43,0), nvl(vRow.YBYL44,0), nvl(vRow.YBYL45,0), nvl(vRow.YBYL46,0), nvl(vRow.YBYL47,0), nvl(vRow.YBYL48,0), nvl(vRow.YBYL49,0), vRow.TSBZBH, LS_YBJKID ) into LS_ZFJE00 from dual; LS_BRZJE0:=ls_HJJE00; LS_HJJE01:=ls_HJJE00; LS_ZFJE01:=LS_ZFJE00; if vRow.FBBH00 not in (1,3,99) then --公费病人 by MZSF-20150610-005 LS_HJJE01:=0; LS_ZFJE01:=0; end if; V_MsgStr:='插入结账表数据SF_JZB000'; --获取结算后账户总余额 (参数：0：门诊 1：住院 参数2：BRID00 参数3：1：门诊 2:住院 参数4：0:可用余额 1:总余额 2：现金余额 3:停用金额) begin select SF_SF_GETBRZHYE(0,PBRID00,1,1) into LS_JSHYE0 from dual; exception when others then LS_JSHYE0:=0; end; Insert into SF_JZB000( JZDH00,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,BCKBCS, TSBZBH,JMJE00,GRZHZF,TCJJZF,ZFJE00,PJH000,JZRQ00,JZSJ00, JZY000,GWYBZ0,FYBJE0,YLZFJE,YBZHYE,SBTCJJ,SBGRZH,jjzfe0, SBZFE0, BJZFE0,SYBXZF,XJFKJE,ZZFKJE,YBDJH0,YBJZRQ,YBJZSJ, YBYL01,YBYL02,YBYL03,YBYL04,YBYL05,YBYL06,YBYL07,YBYL08, YBYL09,YBYL10,YBYL11,YBYL12,YBYL13,YBYL14,YBYL15,YBYL16, YBYL17,YBYL18,YBYL19,YBYL20,YBYL21,YBYL22,YBYL23,YBYL24, YBYL25,YBYL26,YBYL27,YBYL28,YBYL29,YBYL30, YBSM01,YBSM02,YBSM03,YBSM04,YBSM05,YBSM06,YBSM07,YBSM08, MSTID0,MSTZF0,MSTYE0, mxzje0,YBYL31,YBYL32,YBYL33,YBYL34,YBYL35,YBYL36,YBYL37,YBYL38,YBYL39, YBYL40,YBYL41,YBYL42,YBYL43,YBYL44,YBYL45,YBYL46,YBYL47,YBYL48,YBYL49, YBYL50,YBSM09,YBSM10,YBSM11,YBSM12,YBSM13,YBSM14,YBSM15,YBSM16,YBYL51, YBYL52,YBYL53,YBYL54,YBYL55,YBYL56,YBYL57,YBYL58,YBYL59,YBYL60,YBYL61, YBYL62,YBYL63,YBYL64,YBYL65,YBYL66,YBYL67,YBYL68,YBYL69,YBSM17,YBSM18, YBSM19,YBSM20,YBSM21,YBSM22,YBSM23,YBSM24,YBSM25,YBSM26,YBSM27,YBSM28, YBSM29,YBJKID,YBGHH0,MXLSH0,BKC001_MC,AAE140,AAE140_MC,BKE174,BKE174_MC,JSQYE0,JSHYE0, FKJSLX,--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 FKLSH0,--分开结算流水号 FKJZDH,--分开结算单号 FKJZJE,--分开结算金额 YBBXZE,--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) DLTCJJ,--实际医保统筹基金 DLZHZF,--实际医保账户支付 YBQTZE,--医保其它总额 DLSYBX,--商业保险支付(独立于统筹基金金额) DLJZFP,--精准扶贫医疗(独立于统筹基金金额) DLQTJJ,--其他基金支付(独立于统筹基金金额) DLQYBC,--企业补充(独立于统筹基金金额) MZBZJE,--民政补助金额(独立于统筹基金金额) DLSBJE,--商保支付金额(独立于统筹基金金额) GWBZJE,--公务员补助金额(独立于统筹基金金额) DLDBJJ,--大病基金支付金额(独立于统筹基金金额) CJBTCE --产检检查费（bkc064）超出个人自付金额后需补退的差额 ) values(LS_JZDH00,PBRID00,vRow.GHID00,LS_BRZJE0,ls_JZJE00,ls_GFJE00,LS_HJJE01,vRow.BCKBCS, vRow.TSBZBH,vRow.JMJE00,ld_GRZHZF,ld_TCJJZF,LS_ZFJE01,ls_PJH000,lV_CZRQ00,lV_CZSJ00, PCZY000,vRow.Gwybz0,vRow.FYBJE0,vRow.Ylzfje,0,vRow.SBTCJJ,vRow.SBGRZH,vRow.jjzfe0, vRow.SBZFE0,vRow.BJZFE0,vRow.SYBXZF,LS_ZFJE01,vRow.ZZFKJE,vRow.YBDJH0,decode(vRow.YBJKLX,'-1','',lV_CZRQ00),decode(vRow.YBJKLX,'-1','',lV_CZSJ00), vRow.YBYL01,vRow.YBYL02,vRow.YBYL03,vRow.YBYL04,vRow.YBYL05,vRow.YBYL06,vRow.YBYL07,vRow.YBYL08, vRow.YBYL09,vRow.YBYL10,vRow.YBYL11,vRow.YBYL12,vRow.YBYL13,vRow.YBYL14,vRow.YBYL15,vRow.YBYL16, vRow.YBYL17,vRow.YBYL18,vRow.YBYL19,vRow.YBYL20,vRow.YBYL21,vRow.YBYL22,vRow.YBYL23,vRow.YBYL24, vRow.YBYL25,vRow.YBYL26,vRow.YBYL27,vRow.YBYL28,vRow.YBYL29,vRow.YBYL30, vRow.YBSM01,vRow.YBSM02,vRow.YBSM03,vRow.YBSM04,vRow.YBSM05,vRow.YBSM06,vRow.YBSM07,vRow.YBSM08, vRow.MSTID0,vRow.MSTZF0,vRow.MSTYE0, vRow.mxzje0,vRow.YBYL31,vRow.YBYL32,vRow.YBYL33,vRow.YBYL34,vRow.YBYL35,vRow.YBYL36,vRow.YBYL37,vRow.YBYL38,vRow.YBYL39, vRow.YBYL40,vRow.YBYL41,vRow.YBYL42,vRow.YBYL43,vRow.YBYL44,vRow.YBYL45,vRow.YBYL46,vRow.YBYL47,vRow.YBYL48,vRow.YBYL49, vRow.YBYL50,vRow.YBSM09,vRow.YBSM10,vRow.YBSM11,vRow.YBSM12,vRow.YBSM13,vRow.YBSM14,vRow.YBSM15,vRow.YBSM16,vRow.YBYL51, vRow.YBYL52,vRow.YBYL53,vRow.YBYL54,vRow.YBYL55,vRow.YBYL56,vRow.YBYL57,vRow.YBYL58,vRow.YBYL59,vRow.YBYL60,vRow.YBYL61, vRow.YBYL62,vRow.YBYL63,vRow.YBYL64,vRow.YBYL65,vRow.YBYL66,vRow.YBYL67,vRow.YBYL68,vRow.YBYL69,vRow.YBSM17,vRow.YBSM18, vRow.YBSM19,vRow.YBSM20,vRow.YBSM21,vRow.YBSM22,vRow.YBSM23,vRow.YBSM24,vRow.YBSM25,vRow.YBSM26,vRow.YBSM27,vRow.YBSM28, vRow.YBSM29,vRow.YBJKID,vRow.YBGHH0,vRow.MXLSH0,nvl(LS_BKC001_MC,BM_BKC001_MC),nvl(LS_AAE140,BM_AAE140),nvl(LS_AAE140_MC,BM_AAE140_MC),LS_BKE174,LS_BKE174_MC,LS_JSQYE0,LS_JSHYE0, LS_FKJSLX,--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0,--分开结算流水号 LS_FKJZDH,--分开结算单号 LS_FKJZJE,--分开结算金额 LS_YBBXZE,--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_DLTCJJ,--实际医保统筹基金 LS_DLZHZF,--实际医保账户支付 LS_YBQTZE,--医保其它总额 LS_DLSYBX,--商业保险支付(独立于统筹基金金额) LS_DLJZFP,--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ,--其他基金支付(独立于统筹基金金额) LS_DLQYBC,--企业补充(独立于统筹基金金额) LS_DLMZBZ,--民政补助金额(独立于统筹基金金额) LS_DLSBJE,--商保支付金额(独立于统筹基金金额) LS_GWBZJE,--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ,--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE --产检检查费（bkc064）超出个人自付金额后需补退的差额 ); V_MsgStr:='插入结账明细表数据SF_JZMX00'; Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select LS_JZDH00,B.FPXMID,B.XMMC00,sum(A.HJJE00),sum(a.JMJE00),null,null,1,sum(a.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = LS_JZDH00 and D.JFLBID = 1 --JFLBID=1是门诊预交金账户 and D.BRID00 = PBRID00 group by B.FPXMID,B.XMMC00; ----打印类型 N打印发票 Y打印结算单 M不打印发票和结算单 if (LS_DYLX00='Y') and (LS_SFDY00='Y') then Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,CZYKS0, FZPJH0) select SQ_SF_PJSYQK_PJH000.nextval,PLBH00,PLMC00,0,0,0,PCZY000,ls_JZYXM0, Lv_CZRQ00,Lv_CZSj00,LS_SFDY00,'0',ls_BRXM00,ls_BRXB00,PBRID00,LS_CZYKS0, ls_PJH000 from BM_PJLXB0 where PLBH00 = 11;--PLBH00=11是门诊结算单 end if; if (LS_DYLX00='Y') or (LS_DYLX00='M') then --打印结算单 LS_FPXH00:=0; LS_SFDY00:='N'; end if; V_MsgStr:='插入票据表数据SF_PJSYQK'; Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,CZYKS0, FZPJH0) select ls_PJH000,PLBH00,PLMC00,LS_FPXH00,ls_HJJE00,0,PCZY000,ls_JZYXM0, Lv_CZRQ00,Lv_CZSj00,LS_SFDY00,'0',ls_BRXM00,ls_BRXB00,PBRID00,LS_CZYKS0, ls_PJH000 from BM_PJLXB0 where PLBH00 = 1;--PLBH00=1是门诊发票 V_MsgStr:='更新临时表SF_FYXX00_TEMP00'; --update SF_FYXX00_TEMP00 set HJJE00=ls_HJJE00,GFJE00=ls_GFJE00,JZJE00=ls_JZJE00 where BRID00=PBRID00 and ID0000=PID0000; update SF_FYXX00_TEMP00 set JZDH00=LS_JZDH00 Where brid00=pBRID00 and ID0000=PID0000; update SF_FYXX00_TEMPMX set JZDH00=LS_JZDH00 Where brid00=pBRID00 and ID0000=PID0000; update SF_FYXX00_DJH000 set JZDH00=LS_JZDH00 Where brid00=pBRID00 and ID0000=PID0000; update SF_BRZHXX Set TCJE00=nvl(TCJE00,0)+nvl(ld_TCJJZF,0) Where brid00=pBRID00; if nvl(LS_JMJE00,0)>0 then update SF_JZB000 set JMJE00=0 where BRID00=pBRID00 and JZDH00=LS_JZDH00; end if; V_MsgStr:='修改民生通支付表中的结账单号'; if vRow.MSTID0 is not null then update SF_YKTKFB set JZDH00=LS_JZDH00 where brid00=pBRID00 and ID0000=vRow.MSTID0; end if; --结算后更新健康通预扣表的信息DJH000=挂号ID update SF_YKTKFB set DJH000=0,YKTYE0=0,JYJE00=0 where BRID00=PBRID00 and MKDM00='0'; V_MsgStr:='取病人的账户总额'; select nvl(sum(XJYE00),0),nvl(sum(ZZYE00),0),nvl(sum(ZYE000),0),nvl(sum(TYJE00),0) into ls_XJYE00,ls_ZZYE00,ls_ZYE000,ls_TYJE00 from sf_brzhxx where brid00=pBRID00; if LS_ZYE000<0 then PYHMSG0:='执行结算过程SP_SF_FYXX00_JS0000错误:账户余额不足,无法正常结算！'; PSYSMSG:='病人账户余额不足,至少需要补交:'||trim(to_char(abs(LS_ZYE000),'999999990.99'))||'元 '; end if; select nvl(sum(HJJE00),0) into LS_TEMPJE from SF_FYXX00_TEMP00 where BRID00=PBRID00 and ID0000=PID0000; select nvl(sum(BRZJE0),0) into LS_JZBJE0 from SF_JZB000 Where JZDH00=LS_JZDH00 and BRID00=PBRID00; select nvl(sum(HJJE00),0) into LS_FYBJE0 from SF_BRFY00 Where JZDH00=LS_JZDH00 and BRID00=PBRID00; if LS_HJJE00<=0 then PYHMSG0:='费用结算错误:写入结账表数据异常'; PSYSMSG:='执行结算过程SP_SF_FYXX00_JS0000错误'; return; end if; V_MsgStr:='判断本次结算和临时表中的总额是否相等'; if (ls_HJJE00<>vRow.HJJE00)and (vRow.YBJKLX not in ('-1')) then PYHMSG0:='费用结算错误:结算金额和扣费金额不一致！'; PSYSMSG:='本次结算金额和扣费金额不一致！'||'扣费金额:'||to_char(ls_HJJE00)||' 结算金额:'||to_char(vRow.HJJE00); return; end if; V_MsgStr:='判断本次结算费用表数据和结账表的数据是否一致'; if (LS_JZBJE0-LS_FYBJE0)<>0 then PYHMSG0:='费用结算错误:结算表金额和费用表金额不一致！'; PSYSMSG:='本次结算表金额和费用表金额不一致！'||'结算表金额:'||to_char(LS_JZBJE0)||' 费用表金额:'||to_char(LS_FYBJE0); return; end if; --新医保接口结算调用SP_SF_NEWYBJK_JKFHJX解析(补助明细、医保发票费用开始) --ad_YBSM19 医保接口id if LS_YBJKID is not null and LS_YBJKID not in ('0') and vRow.FBBH00 in (3) then --修改SF_YBJKRZ.JZDH00 update SF_YBJKRZ set JZDH00=PJZDH00 where BRID00=PBRID00 and YBJKID=LS_YBJKID; SP_SF_NEWYBJK_JKFHJX( PBRID00,--PBRID00 in number ,--输入:病人ID PJZDH00,--PJZDH00 in number ,--输入:结账单号 LS_YBJKID,--PYBJKID in varchar2 ,--输入:医保接口ID '0',--PMZZYBZ in varchar2 ,--输入:门诊住院标识 0:门诊 1:住院 '1,2',--PJXLX00 in varchar2 ,--输入:解析类型(同时解析传1,2) 1:fplist 2:mzlist 'N',--PCOMMIT in varchar2 default 'N' ,--输入:是否提交事务 Y:提交 N:不提交 LS_ZXZT00,--PZXZT00 out number ,--输出:执行状态 0:失败 1：成功 LS_ERRMSG--PERRMSG out varchar2 --输出:错误信息 ); if LS_ZXZT00=0 then PYHMSG0:=LS_ERRMSG; PSYSMSG:=LS_ERRMSG; end if; end if; exception when no_data_found then PYHMSG0:='错误信息:['||V_MsgStr||']请记录此信息并和系统管理员联系！'; PSYSMSG:=SQLERRM||'执行存储过程SP_SF_FYXX00_JS0000错误'; rollback; when OTHERS then PYHMSG0:='错误信息:['||V_MsgStr||']请记录此信息并和系统管理员联系！'; PSYSMSG:=SQLERRM||'执行存储过程SP_SF_FYXX00_JS0000错误'; rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%